Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# FCQUERY

The FCQUERY function queries the results of a forecast created when the FCEXEC command executed.

You must use the FCQUERY function in combination with other OLAP DML statements as outlined in "Forecasting Using a Forecast Context".

Return Value

The return value depends on the option that you use as described in the tables for this entry.

Syntax

FCQUERY(HANDLELIST|handle-expression option -

[TRIAL trial-num] [CYCLE cycle-num])

Arguments

HANDLELIST

  When you specify the HANDLELIST keyword, the FCQUERY function returns a multiline text expression that is a list of the handles to forecasting contexts that are currently open.

handle-expression

  An INTEGER expression that is the handle to forecast context that you want to query and that was previously opened using the FCOPEN function.

option

  The specific information that you want to retrieve:

  - When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be any of the options that you can specify using the FCSET command and any of the options listed in Table 12-1, "Options That You Can Specify for the Entire Forecast".

  - When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be any of the options listed in Table 12-2, "Options That You Can Specify for an Individual Trial".

  Table 12-1 Options That You Can Specify for the Entire Forecast

  Keyword | Return type | Description
  --- | --- | ---
  HANDLEID | TEXT | The name of the forecasting context when a value was specified when the forecasting context was opened using the FCOPEN command; or `NA` when no name was specified at that time.
  TRIALSRUN | INTEGER | The number of trials for which data is available; or `NA` when no trials were run.

  Table 12-2 Options That You Can Specify for an Individual Trial

  Option | Return Value | Description
  --- | --- | ---
  ALLOCLAST | BOOLEAN | Indicates whether the risk of over-adjustment should be reduced by allocating, instead of forecasting, the last cycle.
  ALPHA | DOUBLE | The value of Alpha for this trial of the forecast. Alpha is the level or baseline parameter that is used for the Single Exponential Smoothing, Double Exponential Smoothing, and Holt-Winters forecasting methods.
  BETA | DOUBLE | The value of Beta for this trial of the forecast. Beta is the trend parameter that controls the estimate of the trend. Beta is used for the Double Exponential Smoothing and Holt-Winters forecasting methods.
  COMPSMOOTH | BOOLEAN | Indicates whether optimization should be done on the median smoothed data series.
  CYCDECAY | DOUBLE | The value of the cyclic decay parameter for this trial of the forecast. Cyclical decay pertains to how seriously Oracle OLAP considers deviations from baseline activity when it performs linear and nonlinear regressions.
  GAMMA | DOUBLE | The value of Gamma for this trial of the forecast. Gamma is the seasonal parameter that is used for the Holt-Winters forecasting method.
  HISTUSED | INTEGER | The number of historical periods actually used, after all leading `NA` values are bypassed.
  MAD | DOUBLE | The mean absolute deviation (MAD) for this trial of the forecast.
  MAPE | DOUBLE | The mean average percent error (MAPE) for this trial of the forecast.
  MAXFCFACTOR | DECIMAL | The upper bound of the forecast data.
  METHOD | TEXT | The forecasting method that Oracle OLAP used for this trial of the forecast. See the METHOD option of the FCSET command for descriptions of the various methods.
  MINFCFACTOR | DECIMAL | The lower bound of the forecast data.
  MPTDECAY | DOUBLE | The value of the parameter that Oracle OLAP used when it adjusted the decay of estimates of base values that were used when it unraveled the predictions on the moving periodic total (MPT) series for this trial of the forecast.
  NCYCLES | INTEGER | The number of cycles specified using the PERIODICITY argument to FCSET.
  PERIODICITY | INTEGER | The length, in periods, of one or more cycles. The return value depends on the way you call the FCQUERY function: When you specify the CYCLE argument, PERIODICITY returns the number of periods in the specified cycle. When you do not specify the CYCLE argument and FCSET ALLOCLAST is `NO`, PERIODICITY returns the product of all cycle lengths. When you do not specify the CYCLE argument and FCSET ALLOCLAST is `YES`, PERIODICITY returns the product of all cycle lengths leaving out the length of the last (least aggregate) cycle.
  RMSE | DOUBLE | The root mean squared error (RMSE) for this trial of the forecast.
  SMOOTHING | BOOLEAN | Indicates whether Oracle OLAP smoothed the data for this trial of the forecast. `YES` indicates that Oracle OLAP smoothed the data; `NO` indicates that Oracle OLAP did not smooth the data.
  TRANSFORM | TEXT | The data filter that Oracle OLAP used for this trial of the forecast. See the TRANSFORM option of the FCSET command for descriptions of the various filters.
  TRENDHOLD | DOUBLE | The value of the trend hold parameter for this trial of the forecast. trend hold parameter that indicates trend reliability in Double Exponential Smoothing and Holt-Winters forecasting methods.

trial-num

  An INTEGER expression that is the number of the trial for which you want to retrieve information.

cycle-num

  An INTEGER expression that specifies a cycle for which you want information from the PERIODICITY option (see Table 12-2, "Options That You Can Specify for an Individual Trial"). When you specified a series of cycles using the PERIODICITY argument in the FCSET command, then the value of cycle-num indicates the position of the cycle of interest in the specified series. For example, assume that `FCSET PERIODICITY <52,7>` was specified. In this case, a cycle-num of 1 returns 52 and a cycle-num of 2 returns 7. When you did not specify a series of cycles using the PERIODICITY argument in the FCSET command, then it is unnecessary to specify this argument.

Notes

Using Options

You can retrieve information about the options specified for the entire forecast or information about a specific trial.

- When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be HANDLEID, TRIALSRUN, or any of the options that you can specify using the FCSET command.

- When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be ALPHA, BETA, CYCDECAY, GAMMA, MAD, MAPE, METHOD, MPTDECAY, RMSE, SMOOTHING, TRANSFORM, or TRENDHOLD.

Accessing Dimensioned Data

When more than one time series was in status when the FCEXEC command was executed, then the TRIALSRUN and the NTRIAL-dimensioned data are also be dimensioned by the extra dimensions of the time-series expression. Although Oracle OLAP treats the value returned by the FCQUERY function as a scalar expression, you can access its dimensioned data in any of the following ways:

- In a FOR loop, FCQUERY returns data for the current values of the FOR dimensions

- In a QUAL function, FCQUERY returns data for the specified values of the qualified dimensions.

- In all other cases, FCQUERY returns data for the first value in status of each of its dimensions.

Examples

Example 12-10 Querying a Forecast

The `autofcst` program illustrated in Example 12-9, "A Forecasting Program" calls a program named `queryall`. The `queryall` program retrieves the characteristics of the trials of the forecast using the following code.

    DEFINE queryall PROGRAM
    PROGRAM
    VARIABLE numtrials INTEGER
    VARIABLE loopindx INTEGER
    numtrials = FCQUERY(hndl trialsrun)
    row numtrials 'TRIALS'
    loopindx = 1
    WHILE loopindx LE numtrials
      DO
        ROW loopindx 'METHOD' FCQUERY(hndl method trial loopindx)
        ROW loopindx 'TRANSFORM' FCQUERY(hndl transform trial loopindx)
        ROW loopindx 'SMOOTHING' FCQUERY(hndl smoothing trial loopindx)
        ROW loopindx 'ALPHA' FCQUERY(hndl alpha trial loopindx)
        ROW loopindx 'BETA' FCQUERY(hndl beta trial loopindx)
        ROW loopindx 'GAMMA' FCQUERY(hndl gamma trial loopindx)
        ROW loopindx 'TRENDHOLD' FCQUERY(hndl trendhold trial loopindx)
        ROW loopindx 'CYCDECAY' FCQUERY(hndl cycdecay trial loopindx)
        row loopindx 'MPTDECAY' FCQUERY(hndl mptdecay trial loopindx)
        ROW loopindx 'MAD' FCQUERY(hndl mad trial loopindx)
        ROW loopindx 'MAPE' FCQUERY(hndl mape trial loopindx)
        ROW loopindx 'RMSE' FCQUERY(hndl rmse trial loopindx)
        loopindx = loopindx + 1
      DOEND
    END

A sample report created from the output of the QUERYALL program follows.

    3 TRIALS
                 1 METHOD     HOLT/WINTERS
                 1 TRANSFORM  TRNOSEA
                 1 SMOOTHING          NO
                 1 ALPHA             0.2
                 1 BETA              0.3
                 1 GAMMA             0.3
                 1 TRENDHOLD         0.8
                 1 CYCDECAY           -1
                 1 MPTDECAY           -1
                 1 MAD         324.97047
                 1 MAPE       23.6192147
                 1 RMSE        389.40202
                 2 METHOD     HOLT/WINTERS
                 2 TRANSFORM  TRNOSEA
                 2 SMOOTHING          NO
                 2 ALPHA             0.2
                 2 BETA              0.3
                 2 GAMMA             0.2
                 2 TRENDHOLD         0.8
                 2 CYCDECAY           -1
                 2 MPTDECAY           -1
                 2 MAD         324.97047
                 2 MAPE       23.6192147
                 2 RMSE        389.40202
                 3 METHOD     HOLT/WINTERS
                 3 TRANSFORM  TRNOSEA
                 3 SMOOTHING          NO
                 3 ALPHA             0.2
                 3 BETA              0.3
                 3 GAMMA             0.1
                 3 TRENDHOLD         0.8
                 3 CYCDECAY           -1
                 3 MPTDECAY           -1
                 3 MAD         324.97047
                 3 MAPE       23.6192147
                 3 RMSE        389.40202